﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Windows.Forms.DataVisualization.Charting;
using ZG_member.BLL;
using System.Net;

namespace ZG_member
{
    public partial class CustomerChart : Form
    {
        public CustomerChart()
        {
            InitializeComponent();
            skinEngine1.SkinFile = Application.StartupPath + ConfigurationManager.AppSettings["Skin"];
            this.Opacity = 0;

        }

        public void ExportExcel(string fileName, DataGridView myDGV)
        {
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.Filter = "*.xlsx|*.xlsx|*.xls|*.xls";
            saveDialog.FilterIndex = 2;
            saveDialog.FileName = fileName + "Excel导出";
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;

            if (saveFileName.IndexOf(":") < 0)
            {
                return;
            }//被点了取消 
            else
            {
                //MessageBoxTimeout.Show("正在导出···，请稍等！", "系统提示", 2000);
            }
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Office.Excel组件");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
            //写入标题
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {
                worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
            }
            //写入数值
            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                }
                catch (Exception ex)
                {
                    //fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                }
            }
            xlApp.Quit();
            GC.Collect();//强行销毁 
            MessageBox.Show(saveFileName + "的资料保存成功", "提示", MessageBoxButtons.OK);
        }


        private void btnsubmit_Click(object sender, EventArgs e)
        {
          
            memberInfo memInfo = new memberInfo();
            string unit = "";
            DataTable dt;
            string AgeOne = this.txtAge1.Text==""?"0":this.txtAge1.Text;
            string AgeTwo = this.txtAge2.Text==""?"500":this.txtAge2.Text;
            string Age3 = "";
            string money1=this.txtMoney1.Text==""?"0":this.txtMoney1.Text;
            string money2=this.txtMoney2.Text == "" ? "100000000000" : this.txtMoney2.Text;
            string money3 = "";

            if (Convert.ToDecimal(money1) > Convert.ToDecimal(money2))      
            {
                money3 = money2;
                money2 = money1;
                money1 = money3;
            }
            if (Convert.ToInt32(AgeOne) > Convert.ToInt32(AgeTwo))
            {
                Age3=AgeTwo;
                AgeTwo=AgeOne;
                AgeOne=Age3;
            }
            string UserSex = this.cbxSex.Text;
            string sex = "";
            string MemberType = "";
            string jiedairen = "";
             if (this.txtjiedairen.Text != "")
            {
                jiedairen = " and tuiguangren like '%"+this.txtjiedairen.Text+"%'";
            }

            if (UserSex == "男")
            {
                sex = " and sex=1";
            }
            if (UserSex == "女")
            {
                sex = " and sex=0";
            }
            if (cbxMemberType.Text == "团购客户")
            {
                MemberType = " and MemberType='团购客户'";
            }
            if (cbxMemberType.Text == "个人收藏")
            {
                MemberType = " and MemberType='个人收藏'";
            }
            if (cbxMemberType.Text == "选礼客户")
            {
                MemberType = " and MemberType='选礼客户'";
            }
            StringBuilder sb =new StringBuilder();
            goodsType gt = new goodsType();
            ZG_member.Model.goodsType gtModel = new ZG_member.Model.goodsType();
            string goodsType=this.cbxGoodsType.Text;
            gtModel = gt.GetModelStr(goodsType);

            switch (cbxDateUnit.Text)
            {
                case "月":
                    unit = "MM";
                    sb.Clear();
                    if (cbxGoodsType.Text == "全部")
                    {
                        string sss = "select CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(MM,date))+'月' as dateU,COUNT(id) as 人数 from (select * from memberInfo m where DATEDIFF(YEAR,birthDay,GETDATE())>=" + AgeOne + " and DATEDIFF(YEAR,birthDay,GETDATE())<=" + AgeTwo + "" + sex + "" + MemberType + "" + jiedairen + ") as m,(select memberCardId,date,SUM(consumeMoney)as 消费总计 from wasteBook where date>='" + dtpStart.Value + "'and date<='" + dtpEnd.Value + "' group by date,memberCardId) as w where m.memberId=w.memberCardId and w.消费总计>=" + money1 + " and w.消费总计<=" + money2 + " group by CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(MM,date))+'月'";
                        dt = memInfo.GetListSql("select CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(MM,date))+'月' as dateU,COUNT(id) as 人数 from (select * from memberInfo m where DATEDIFF(YEAR,birthDay,GETDATE())>=" + AgeOne + " and DATEDIFF(YEAR,birthDay,GETDATE())<=" + AgeTwo + "" + sex + "" + MemberType + "" + jiedairen + ") as m,(select memberCardId,date,SUM(consumeMoney)as 消费总计 from wasteBook where date>='" + dtpStart.Value + "'and date<='" + dtpEnd.Value + "' group by date,memberCardId) as w where m.memberId=w.memberCardId and w.消费总计>=" + money1 + " and w.消费总计<=" + money2 + " group by CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(MM,date))+'月'").Tables[0];
                    }
                    else
                    {
                        sb.Append("select CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(MM,date))+'月' as dateU,COUNT(id) as 人数 from (select * from memberInfo m where DATEDIFF(YEAR,birthDay,GETDATE())>=" + AgeOne + " and DATEDIFF(YEAR,birthDay,GETDATE())<=" + AgeTwo + "" + sex + "" + MemberType + "" + jiedairen + ") as m,");
                        sb.Append("(select memberCardId,date,SUM(goodscount*goodsprice)as 消费总计 from ");
                        sb.Append("(select * from(select * from(select id as w_id,memberCardId,date from wasteBook where date>='" + this.dtpStart.Value + "'and date<='" + this.dtpEnd.Value + "')as w,goodsInfo as g where w.w_id=g.wasteBookId) as wg) as yongyouGoods,");
                        sb.Append("(select* from(select id as TypeId,typeName from goodsType where id="+gtModel.id+") as gt,goods where goods.goodsTypeId=gt.TypeId)as baokuoGoods ");
                        sb.Append("where yongyouGoods.gid= baokuoGoods.goodsId group by memberCardId,date) as w ");
                        sb.Append("where m.memberId=w.memberCardId and w.消费总计>=" +money1+ " and w.消费总计<=" + money2 + " group by CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(MM,date))+'月'");
                        dt = memInfo.GetListSql(sb.ToString()).Tables[0];
                    }
                    break;
                case "季度":
                    unit = "Q";
                    sb.Clear();
                    if (cbxGoodsType.Text == "全部")
                    {
                        dt = memInfo.GetListSql("select CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(Q,date))+'季度' as dateU,COUNT(id) as 人数 from (select * from memberInfo m where DATEDIFF(YEAR,birthDay,GETDATE())>=" + AgeOne + " and DATEDIFF(YEAR,birthDay,GETDATE())<=" + AgeTwo + "" + sex + "" + MemberType + "" + jiedairen + ") as m,(select memberCardId,date,SUM(consumeMoney)as 消费总计 from wasteBook where date>='" + dtpStart.Value + "'and date<='" + dtpEnd.Value + "' group by date,memberCardId) as w where m.memberId=w.memberCardId and w.消费总计>=" + money1 + " and w.消费总计<=" + money2 + " group by CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(Q,date))+'季度'").Tables[0];
                    }
                    else
                    {
                        sb.Append("select CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(Q,date))+'季度' as dateU,COUNT(id) as 人数 from (select * from memberInfo m where DATEDIFF(YEAR,birthDay,GETDATE())>=" + AgeOne + " and DATEDIFF(YEAR,birthDay,GETDATE())<=" + AgeTwo + "" + sex + "" + MemberType + "" + jiedairen + ") as m,");
                        sb.Append("(select memberCardId,date,SUM(goodscount*goodsprice)as 消费总计 from ");
                        sb.Append("(select * from(select * from(select id as w_id,memberCardId,date from wasteBook where date>='" + this.dtpStart.Value + "'and date<='" + this.dtpEnd.Value + "')as w,goodsInfo as g where w.w_id=g.wasteBookId) as wg) as yongyouGoods,");
                        sb.Append("(select* from(select id as TypeId,typeName from goodsType where id="+gtModel.id+") as gt,goods where goods.goodsTypeId=gt.TypeId)as baokuoGoods ");
                        sb.Append("where yongyouGoods.gid= baokuoGoods.goodsId group by memberCardId,date) as w ");
                        sb.Append("where m.memberId=w.memberCardId and w.消费总计>=" + money1 + " and w.消费总计<=" + money2 + " group by CONVERT(varchar,DATEPART(YYYY,date))+'年'+CONVERT(varchar,DATEPART(Q,date))+'季度'");
                        dt = memInfo.GetListSql(sb.ToString()).Tables[0];
                    }
                    break;
                case "年":
                    unit = "YYYY";
                    sb.Clear();
                    if (cbxGoodsType.Text == "全部")
                    {
                        dt = memInfo.GetListSql("select CONVERT(varchar,DATEPART(YYYY,date))+'年' as dateU,COUNT(id) as 人数 from (select * from memberInfo m where DATEDIFF(YEAR,birthDay,GETDATE())>=" + AgeOne + " and DATEDIFF(YEAR,birthDay,GETDATE())<=" + AgeTwo + "" + sex + "" + MemberType + "" + jiedairen + ") as m,(select memberCardId,date,SUM(consumeMoney)as 消费总计 from wasteBook where date>='" + dtpStart.Value + "'and date<='" + dtpEnd.Value + "' group by date,memberCardId) as w where m.memberId=w.memberCardId and w.消费总计>=" + money1 + " and w.消费总计<=" + money2 + " group by CONVERT(varchar,DATEPART(YYYY,date))+'年'").Tables[0];
                    }
                    else
                    {
                        sb.Append("select CONVERT(varchar,DATEPART(YYYY,date))+'年' as dateU,COUNT(id) as 人数 from (select * from memberInfo m where DATEDIFF(YEAR,birthDay,GETDATE())>=" + AgeOne + " and DATEDIFF(YEAR,birthDay,GETDATE())<=" + AgeTwo + "" + sex + "" + MemberType + "" + jiedairen + ") as m,");
                        sb.Append("(select memberCardId,date,SUM(goodscount*goodsprice)as 消费总计 from ");
                        sb.Append("(select * from(select * from(select id as w_id,memberCardId,date from wasteBook where date>='" + this.dtpStart.Value + "'and date<='" + this.dtpEnd.Value + "')as w,goodsInfo as g where w.w_id=g.wasteBookId) as wg) as yongyouGoods,");
                        sb.Append("(select* from(select id as TypeId,typeName from goodsType where id="+gtModel.id+") as gt,goods where goods.goodsTypeId=gt.TypeId)as baokuoGoods ");
                        sb.Append("where yongyouGoods.gid= baokuoGoods.goodsId group by memberCardId,date) as w ");
                        sb.Append("where m.memberId=w.memberCardId and w.消费总计>=" + money1 + " and w.消费总计<=" + money2 + " group by CONVERT(varchar,DATEPART(YYYY,date))+'年'");
                        dt = memInfo.GetListSql(sb.ToString()).Tables[0];
                    }

                    break;
                default:
                    unit = "DD";
                    sb.Clear();
                    if (cbxGoodsType.Text == "全部")
                    {
                        dt = memInfo.GetListSql("select date as dateU,COUNT(id) as 人数 from (select * from memberInfo m where DATEDIFF(YEAR,birthDay,GETDATE())>=" + AgeOne + " and DATEDIFF(YEAR,birthDay,GETDATE())<=" + AgeTwo + "" + sex + "" + MemberType + "" + jiedairen + ") as m,(select memberCardId,date,SUM(consumeMoney)as 消费总计 from wasteBook where date>='" + dtpStart.Value + "'and date<='" + dtpEnd.Value + "' group by date,memberCardId) as w where m.memberId=w.memberCardId and w.消费总计>=" + money1 + " and w.消费总计<=" + money2 + " group by date").Tables[0];
                    }
                    else
                    {
                        sb.Append("select date as dateU,COUNT(id) as 人数 from (select * from memberInfo m where DATEDIFF(YEAR,birthDay,GETDATE())>=" + AgeOne + " and DATEDIFF(YEAR,birthDay,GETDATE())<=" + AgeTwo + "" + sex + "" + MemberType + "" + jiedairen + ") as m,");
                        sb.Append("(select memberCardId,date,SUM(goodscount*goodsprice)as 消费总计 from ");
                        sb.Append("(select * from(select * from(select id as w_id,memberCardId,date from wasteBook where date>='" + this.dtpStart.Value + "'and date<='" + this.dtpEnd.Value + "')as w,goodsInfo as g where w.w_id=g.wasteBookId) as wg) as yongyouGoods,");
                        sb.Append("(select* from(select id as TypeId,typeName from goodsType where id="+gtModel.id+") as gt,goods where goods.goodsTypeId=gt.TypeId)as baokuoGoods ");
                        sb.Append("where yongyouGoods.gid= baokuoGoods.goodsId group by memberCardId,date) as w ");
                        sb.Append("where m.memberId=w.memberCardId and w.消费总计>=" + money1 + " and w.消费总计<=" + money2 + " group by date");
                        dt = memInfo.GetListSql(sb.ToString()).Tables[0];
                    }
                    break;
            }




            //DataTable dt = memInfo.GetListSql("select convert(varchar,DATEPART (YYYY , date ))+ '年' + convert(varchar,DATEPART (MM , date )) as dateU,sum(consumeMoney) as 消费,sum(savingMoney) as 储值 from wasteBook where date>='" + dtpStart.Value + "' and date<='" + dtpEnd.Value + "' group by DATEPART (YYYY , date ),DATEPART (" + unit + " , date ) order by DATEPART (YYYY , date ),DATEPART (" + unit + " , date )").Tables[0];

            chart1.DataSource = null;

            chart1.DataSource = dt;
            chart1.Series["发卡数"].XValueMember = "dateU";
            chart1.Series["发卡数"].YValueMembers = "人数";
            //chart1.Series["退卡数"].XValueMember = "dateU";
            //chart1.Series["退卡数"].YValueMembers = "退卡数";
            chart1.DataBind();

            chart1.ChartAreas["ChartArea1"].AxisX.Title = cbxDateUnit.Text;

            dgvDate.DataSource = dt;
            if (dt.Rows.Count > 9) //当X轴超过9时就会显示不完全
            {
                //设置X轴坐标的间隔为1
                chart1.ChartAreas["ChartArea1"].AxisX.Interval = 1;
                //设置X轴坐标的偏移为1
                chart1.ChartAreas["ChartArea1"].AxisX.IntervalOffset = 1;
                //设置交错显示，例如数据多时可分两行显示
                chart1.ChartAreas["ChartArea1"].AxisX.LabelStyle.IsStaggered = true;
            }

        }

        private void CustomerChart_Load(object sender, EventArgs e)
        {

            goodsType goods = new goodsType();
            ZG_member.Model.goodsType goodsModel = new ZG_member.Model.goodsType();
            DataSet ds = goods.GetAllList("");
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                cbxGoodsType.Items.Add(ds.Tables[0].Rows[i]["typeName"]);
            }
            cbxGoodsType.Items.Add("全部");
            cbxGoodsType.SelectedIndex = cbxGoodsType.Items.Count - 1;
            this.timer1.Start();
            this.cbxSex.SelectedIndex = 2;
            this.cbxMemberType.Text="全部";
            cbxDateUnit.Text = "月";
            skinEngine1.SkinFile = Application.StartupPath + ConfigurationManager.AppSettings["Skin"];
            dtpStart.Value = dtpStart.Value.AddMonths(-1);
            Series s1 = new Series("发卡数");
            chart1.Series.Add(s1);
            chart1.Series["发卡数"].IsValueShownAsLabel = true;
            //Series s2 = new Series("退卡数");
            //chart1.Series.Add(s2);
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            ExportExcel(" ", dgvDate);

        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            this.Left += 2;
            this.Opacity += 0.1;
            if (Opacity == 1)
            {
                this.timer1.Stop();

            }

        }

        private void CustomerChart_FormClosing(object sender, FormClosingEventArgs e)
        {
            
        }

        private void timer2_Tick(object sender, EventArgs e)
        {
            this.Top = this.Top + 10;
            this.Opacity = this.Opacity - 0.06;
            if (Opacity <= 0)
            {
                this.timer2.Stop();
                this.Close();
            }
        }

        int flag = 0;
        private void CustomerChart_FormClosing_1(object sender, FormClosingEventArgs e)
        {
            if (flag == 0)
            {
                e.Cancel = true;
                flag = 1;    
                this.timer2.Start();
            }
           
                        
           

        }

        private void txtMoney1_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!(((e.KeyChar >= '0') && (e.KeyChar <= '9')) || e.KeyChar <= 31))
            {
                if (e.KeyChar == '.')
                {
                    if (((TextBox)sender).Text.Trim().IndexOf('.') > -1)
                        e.Handled = true;
                }
                else
                e.Handled = true;
            }
            else
            {
                if (e.KeyChar <= 31)
                {
                    e.Handled = false;
                }
                else if (((TextBox)sender).Text.Trim().IndexOf('.') > -1)
                {
                    if (((TextBox)sender).Text.Trim().Substring(((TextBox)sender).Text.Trim().IndexOf('.') + 1).Length >= 4)
                        e.Handled = true;
                }
            }
        }

        private void txtMoney2_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!(((e.KeyChar >= '0') && (e.KeyChar <= '9')) || e.KeyChar <= 31))
            {
                if (e.KeyChar == '.')
                {
                    if (((TextBox)sender).Text.Trim().IndexOf('.') > -1)
                        e.Handled = true;
                }
                else
                e.Handled = true;
            }
            else
            {
                if (e.KeyChar <= 31)
                {
                    e.Handled = false;
                }
                else if (((TextBox)sender).Text.Trim().IndexOf('.') > -1)
                {
                    if (((TextBox)sender).Text.Trim().Substring(((TextBox)sender).Text.Trim().IndexOf('.') + 1).Length >= 4)
                        e.Handled = true;
                }
            }
        }

   
        private void txtAge2_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!(((e.KeyChar >= '0') && (e.KeyChar <= '9')) || e.KeyChar <= 31))
            {
                //if (e.KeyChar == '.')
                //{
                //    if (((TextBox)sender).Text.Trim().IndexOf('.') > -1)
                //        e.Handled = true;
                //}
                //else
                e.Handled = true;
            }
            else
            {
                if (e.KeyChar <= 31)
                {
                    e.Handled = false;
                }
                else if (((TextBox)sender).Text.Trim().IndexOf('.') > -1)
                {
                    if (((TextBox)sender).Text.Trim().Substring(((TextBox)sender).Text.Trim().IndexOf('.') + 1).Length >= 4)
                        e.Handled = true;
                }
            }
        }

   

    
      
    }
}
